package com.merck.service.exportTemp;

import com.amazonaws.HttpMethod;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.*;
import com.merck.job.GenerateExcelTemplate;
import com.merck.service.export.GenerateExcelImpl;
import com.merck.service.export.SqlStr;
import com.merck.service.export.TitleMap;
import com.merck.service.impl.ImportDataServiceImpl;
import com.merck.service.read.ReadExcelImpl;
import com.merck.utils.DateUtils;
import com.merck.utils.SendMail;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class GenerateTemp {
    private Logger log = Logger.getLogger(GenerateTemp.class);

    public void exportExcelTemp() {
        Properties pro = new Properties();
        Properties pros = new Properties();
        InputStream resourceAsStream = null;
        InputStream configStream = null;
        AmazonS3 s3Client = null;
        String configBucketName = null;
        String configRemoteFileName = null;
        try {
            System.out.println("读取配置文件开始-----------");
            configStream = GenerateExcelImpl.class.getClassLoader().getResourceAsStream("db.properties");
            pros.load(configStream);
            configBucketName = (String) pros.get("config.bucket");
            configRemoteFileName = (String) pros.get("config.remote");

//            resourceAsStream = ReadExcelImpl.class.getResourceAsStream("/config.properties");
//            pro.load(resourceAsStream);
            s3Client = openS3ClientByEC2();
            GetObjectRequest getObjectRequest = new GetObjectRequest(configBucketName, configRemoteFileName);
            S3Object s3Object = s3Client.getObject(getObjectRequest);
            resourceAsStream = s3Object.getObjectContent();
            pro.load(resourceAsStream);
            String configName = (String)pro.get("template.export.bucket.name");
            System.out.println("读取配置文件中的模板路径 = " + configName);
            System.out.println("读取配置文件结束-----------");

            List<File> attachments = new ArrayList<>();
            String remoteFileName = null;
            ImportDataServiceImpl importData = new ImportDataServiceImpl();
            //当年 2021
            //从表中获取年份和季度
            String sql = "select * from lego_kpi_calculation.ads_kpi_manual_template_qf where year is not null and quarter is not null limit 1";
            List<Map<String, Object>> list = importData.queryForListMap(sql);
            if (CollectionUtils.isEmpty(list)) {
                throw new RuntimeException("数据的年和季度为空！");
            }
            String currentQuarterly = "Q" + (String) list.get(0).get("quarter");
            String currentYear = (String)list.get(0).get("year");

//            String currentYear = DateUtils.getCurrentYear();
//            String currentQuarterly = "Q" + DateUtils.getCurrentQuarterly();
            String dateStr = DateUtils.format(new Date(), "yyyyMMddHHmmss");
            boolean delete;
            String bucketNameExport = (String) pro.get("template.export.bucket.name");
            String backUpBucketName = (String) pro.get("backup.template.export.bucket.name");
            String exportPath = (String) pro.get("template.export.file.name") + currentYear + " " + currentQuarterly;
            String backupPath = (String) pro.get("backup.template.export.file.name") + "backup" + dateStr + "/" + currentYear + " " + currentQuarterly;

            String name = currentYear + " " + currentQuarterly;
            //SHEET 页对应的数据sql
            Map<String, String> map = null;
            Map<String, Map<String, String>> mapTitle = null;
            /**导出Quarterly KPI Manual Added FBU*/
            mapTitle = TempTitleMap.getTempTitleMap("FBU");
            map = new LinkedHashMap<>();
            map.put("FBU_REP_FD_C FD_S FD_P", SqlStrTemp.FBU_REP_FD_C_FD_S_FD_P);
            map.put("FBU_DSM_FD_BA", SqlStrTemp.FBU_DSM_FD_BA);
            map.put("FBU_DSM_FD_S FD_C FD_P", SqlStrTemp.KPI_FBU_DSM_FD_S_FD_C_FD_P);
            map.put("FBU_FT DM_FT", SqlStrTemp.FBU_FT_DM_FT);
            map.put("FBU_RSD&NAD", SqlStrTemp.KPI_FBU_RSD_NAD);
            File file1 = new File(name + " Quarterly KPI Manual Added FBU.xlsx");
            attachments.add(file1);
            remoteFileName = exportPath + " Quarterly KPI Manual Added FBU.xlsx";
            importData.exportDataExcleApp(map, file1, "excle", mapTitle, "temp");
            GenerateExcelIntoS3(remoteFileName, bucketNameExport, file1);
            //备份
            remoteFileName = backupPath + " Quarterly KPI Manual Added FBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, backUpBucketName, file1);
//        delete = file.delete();
            log.info("生成FBU excel模板结束");
            /**导出Quarterly KPI Manual Added GBU*/
            mapTitle = TempTitleMap.getTempTitleMap("GBU");
            map = new LinkedHashMap<>();
            map.put("KPI_GBU_REP_CHD", SqlStrTemp.KPI_GBU_REP_CHD);
            map.put("KPI_GBU_REP_ST_TD_DB_CV_MIX", SqlStrTemp.KPI_GBU_REP_ST_TD_DB_CV_MIX);
            map.put("KPI_GBU_DSM_HA", SqlStrTemp.KPI_GBU_DSM_HA);
            map.put("KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD", SqlStrTemp.KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD);
            map.put("KPI_GBU_RSM", SqlStrTemp.KPI_GBU_RSM);
            File file2 = new File(name + " Quarterly KPI Manual Added GBU.xlsx");
            attachments.add(file2);
            remoteFileName = exportPath + " Quarterly KPI Manual Added GBU.xlsx";
            importData.exportDataExcleApp(map, file2, "excle", mapTitle, "temp");
            GenerateExcelIntoS3(remoteFileName, bucketNameExport, file2);
            //备份
            remoteFileName = backupPath + " Quarterly KPI Manual Added GBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, backUpBucketName, file2);
            log.info("生成GBU excel模板结束");
            /**导出Quarterly KPI Manual Added KA*/
            mapTitle = TempTitleMap.getTempTitleMap("KA");
            map = new LinkedHashMap<>();
            map.put("KPI_KA_DSM_KA", SqlStrTemp.KPI_KA_DSM_KA);

            File file3 = new File(name + " Quarterly KPI Manual Added KA.xlsx");
            attachments.add(file3);
            remoteFileName = exportPath + " Quarterly KPI Manual Added KA.xlsx";
            importData.exportDataExcleApp(map, file3, "excle", mapTitle, "temp");
            GenerateExcelIntoS3(remoteFileName, bucketNameExport, file3);
            //备份
            remoteFileName = backupPath + " Quarterly KPI Manual Added KA.xlsx";
            GenerateExcelIntoS3(remoteFileName, backUpBucketName, file3);
            log.info("生成KA excel模板结束");

            /**Quarterly KPI Manual Added OBU*/
            mapTitle = TempTitleMap.getTempTitleMap("OBU");
            map = new LinkedHashMap<>();
            map.put("KPI_OBU_REP_Erbitux", SqlStrTemp.KPI_OBU_REP_Erbitux);
            map.put("KPI_OBU_REP_Xalkori", SqlStrTemp.KPI_OBU_REP_Xalkori);
            map.put("KPI_OBU_DSM", SqlStrTemp.KPI_OBU_DSM);
            map.put("KPI_OBU_RSM", SqlStrTemp.KPI_OBU_RSM);

            File file4 = new File(name + " Quarterly KPI Manual Added OBU.xlsx");
            attachments.add(file4);
            remoteFileName = exportPath + " Quarterly KPI Manual Added OBU.xlsx";
            importData.exportDataExcleApp(map, file4, "excle", mapTitle, "temp");
            GenerateExcelIntoS3(remoteFileName, bucketNameExport, file4);
            //备份
            remoteFileName = backupPath + " Quarterly KPI Manual Added OBU.xlsx";
            GenerateExcelIntoS3(remoteFileName, backUpBucketName, file4);
            log.info("生成KA excel模板结束");

            String currentDateAsString = DateUtils.getCurrentDateAsString();
            SendMail.sendMail(null, null, "导出模板数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.SUCESS_SEND_CONTENT, attachments);
            delete = file1.delete();
            delete = file2.delete();
            delete = file3.delete();
            delete = file4.delete();
        } catch (Exception e) {
            String currentDateAsString = DateUtils.getCurrentDateAsString();
            SendMail.sendMail(null, null, "导出模板数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.FAILURE_SEND_CONTENT, null);
            log.error(e.getMessage());
        } finally {
            if (resourceAsStream != null) {
                try {
                    resourceAsStream.close();
                } catch (IOException e) {
                    log.error(e.getMessage());
                }
            }
        }

    }

    private void GenerateExcelIntoS3(String remoteFileName, String bucketName, File file) {
//        File file = new File("temp.xlsx");
//        log.info("开始生成excel表格");
//        String bucketName = (String) pro.get("export.bucket.name");
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);

        //S3环境运行代码开始
//        //把生成的Excel上传到S3，上传的路径为上面两个参数定义 ，为 s3://qa-lego-deployment/templates/ + fileName
//        //可以在HUE中查看
        AmazonS3 s3Client = null;
        try {
            s3Client = openS3ClientByEC2();

            PutObjectRequest poRequest = new PutObjectRequest(bucketName, remoteFileName, file).withCannedAcl(CannedAccessControlList.Private);
            s3Client.putObject(poRequest);
            GeneratePresignedUrlRequest urlRequest = new GeneratePresignedUrlRequest(bucketName, remoteFileName, HttpMethod.POST);
            s3Client.generatePresignedUrl(urlRequest);
//            file.delete();
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new RuntimeException("将文件写入S3出错" + e.getMessage());
        } finally {
            if (s3Client != null) {
                s3Client.shutdown();
            }
        }
    }

    public static AmazonS3 openS3ClientByEC2() {
        AmazonS3 s3Client = null;
        try {
            s3Client = AmazonS3ClientBuilder.defaultClient();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return s3Client;
    }

}
